﻿
-- Ajout des colonnes nécessaires aux tables générées.
set nocount on

declare @name varchar(50),
		@sql nvarchar(4000)

declare cur cursor for Select name from sys.tables where name like 'gen_%'
open cur

fetch next from cur into @name
While (@@FETCH_STATUS <> -1)
BEGIN
	set @sql = null
	
	set @sql = 
	'
		ALTER TABLE '+ @name + '
		ADD [CreatedOn] datetime null, 
		[Depth] int null, 
		[ParentNodeId] uniqueidentifier, 
		[Path] varchar(500), 
		[Position] smallint, 
		[TypeId] uniqueidentifier, 
		[ModifiedOn] datetime null
	' 
	
	exec sp_executesql @sql
	
	fetch next from cur into @name
END

CLOSE cur
DEALLOCATE cur

-- Remplir les tables générées avec les données de Medianamik_Node


set nocount on

declare @name varchar(50),
		@sql nvarchar(4000)

declare cur cursor for Select name from sys.tables where name like 'gen_%'
open cur

fetch next from cur into @name
While (@@FETCH_STATUS <> -1)
BEGIN
	set @sql = null
	
	set @sql = 
	'
		update '+@name+' set
		[CreatedOn]=vw.[CreatedOn], 
		[Depth]=vw.[Depth], 
		[ParentNodeId]=vw.[ParentNodeId],
		[Path]=vw.[Path], 
		[Position]=vw.[Position], 
		[TypeId]=vw.[TypeId],
		[ModifiedOn]=vw.[ModifiedOn]
		from '+@name+' src inner join Medianamik_Node vw on src.NodeId = vw.NodeId

	' 
	
	exec sp_executesql @sql
	
	fetch next from cur into @name
END

CLOSE cur
DEALLOCATE cur
